﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_raw_material
{
    public DAL_tbl_raw_material()
    {

    }



    public List<tbl_raw_material> select(String RAW_MATERIAL_ID)
    {
        List<tbl_raw_material> list = new List<tbl_raw_material>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select RAW_MATERIAL_ID,RAW_MATERIAL,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_raw_material where RAW_MATERIAL_ID=@RAW_MATERIAL_ID";
        cmd.Parameters.AddWithValue("@RAW_MATERIAL_ID", RAW_MATERIAL_ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_raw_material()
            {
                RAW_MATERIAL_ID = dr["RAW_MATERIAL_ID"].ToString(),
                RAW_MATERIAL = dr["RAW_MATERIAL"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_raw_material> selectAll()
    {
        List<tbl_raw_material> list = new List<tbl_raw_material>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select RAW_MATERIAL_ID,RAW_MATERIAL,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_raw_material Order By RAW_MATERIAL_ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_raw_material()
            {
                RAW_MATERIAL_ID = dr["RAW_MATERIAL_ID"].ToString(),
                RAW_MATERIAL = dr["RAW_MATERIAL"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public int insert(String RAW_MATERIAL, bool IS_ACTIVE, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_raw_material(RAW_MATERIAL, IS_ACTIVE, CREATED_BY, CREATED_DATE)
                        values(@RAW_MATERIAL, @IS_ACTIVE, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RAW_MATERIAL", RAW_MATERIAL);
        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(String RAW_MATERIAL_ID, String RAW_MATERIAL, bool IS_ACTIVE, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_raw_material SET RAW_MATERIAL=@RAW_MATERIAL, IS_ACTIVE=@IS_ACTIVE, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE RAW_MATERIAL_ID=@RAW_MATERIAL_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RAW_MATERIAL_ID", RAW_MATERIAL_ID);
        cmd.Parameters.AddWithValue("@RAW_MATERIAL", RAW_MATERIAL);
        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(String RAW_MATERIAL_ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_raw_material WHERE RAW_MATERIAL_ID=@RAW_MATERIAL_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RAW_MATERIAL_ID", RAW_MATERIAL_ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }

    public DataTable fetchSuppliers()
    {
        string cmd = "SELECT user_code, supplier_Name, "
        + " CONCAT(supplier_address,', ',supplier_City,', ',Supplier_state) AS Address, "
    + " CONCAT (supplier_primary_Contact_no, ' ',Supplier_secondary_contact_number) AS Contact, " 
    + " Supplier_Email_id, Supplier_skype_id "
    + " FROM tbl_supplier_master  "
    + " WHERE is_active =1";
        try
        {
            return DBUtil.ExecuteDataTable(cmd);
        }
        finally
        {

            cmd = null;
        }
      

    }

}